﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace ProjControleDAL
{
    public class Dados
       
    {
        private String conexao;
        private SqlConnection con;

        public Dados()//método construtor
        {
            //conexao = "data source= 192.168.1.101,1433\\SQLSERVER; initial catalog=db_proj_controle; User Id= Willian Dias;Password=acesso225533;";
            //conexao = "data source= 186.219.66.169,1433\\SQLSERVER; initial catalog=db_proj_controle; User Id= Willian Dias;Password=acesso225533;";
            conexao = "data source=willian-pc\\SQLSERVER; initial catalog=db_proj_controle; User Id = Willian Dias; Password=acesso225533";
            con = new SqlConnection(conexao);
        }
        //abrir a conexao
        private void abrirConexao()//abrir a conexao com o SGBD
        {
            try
            {
                con.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        private void fecharConexao()
        {
            try
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //update,delete e insert sem retorno de código
        public void executaComando(SqlCommand cmd)
        {
            try
            {
                abrirConexao();
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
                fecharConexao();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //insert que retorna um valor
        public int executaComandoAI(SqlCommand cmd, string tabela)
        {
            int codigo;
            object result;
            try
            {
                abrirConexao();
                cmd.Connection = con;
                cmd.CommandText += "; select @@identity " + tabela;
                result = cmd.ExecuteScalar();
                codigo = Convert.ToInt32(result);
                fecharConexao();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return codigo;
        }
        //select
        public DataTable executaConsulta(SqlCommand cmd)
        {
            DataTable tab = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {
                cmd.Connection = con;
                da.SelectCommand = cmd;
                da.Fill(tab);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            da = null;
            return tab;
        }
        
        public void preencheCombo(ComboBox combox,String tabela,String codigo,String descricao,String filtro,String ordem)
        {
            SqlCommand cmd;
            DataSet ds;
            SqlDataAdapter da;
            string sql;

            try
            {
                cmd = new SqlCommand();
                cmd.Connection = con;
                sql = "select * from " + tabela;
                if (filtro != "")
                {
                    sql = sql + " where " + filtro;
                }
                if (ordem != "")
                {
                    sql = sql + " order by " + ordem;
                }
                cmd.CommandText = sql;
                da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                ds = new DataSet();
                //preenchendo o dataset
                da.Fill(ds,"tab");
                //associando a datatable ao combobox
                combox.DisplayMember = null;
                combox.ValueMember = null;
                combox.DataSource = null;
                combox.DataSource = ds.Tables[0];
                combox.DisplayMember = descricao;
                combox.ValueMember = codigo;

            }
            catch (Exception ex)
            {
                
                throw ex;
            }
        }
    }
}
